
USE [BSG]
GO
/****** Object:  View [dbo].[vw_Stock]    Script Date: 12/04/2015 12:02:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[vw_Stock]
AS
SELECT     dbo.VW_Vouchers.SessionID, dbo.VW_Vouchers.VoucherNo, dbo.VW_Vouchers.VoucherDate, dbo.VW_Vouchers.BankAccountID AS PartyID, 
                      dbo.VW_Vouchers.BankAccountTitle AS PartyName, dbo.VW_Vouchers.AccountNo, dbo.VW_Vouchers.AccountTitle, dbo.vw_Items.GroupID, 
                      dbo.vw_Items.ItemID, dbo.vw_Items.ItemName, SUM(ISNULL(dbo.VW_Vouchers.Qty, 0)) AS QTY, SUM(ISNULL(dbo.VW_Vouchers.Edel, 0)) AS Edel, 
                      SUM(ISNULL(dbo.VW_Vouchers.Erec, 0)) AS Erec
FROM         dbo.vw_Items LEFT OUTER JOIN
                      dbo.VW_Vouchers ON dbo.vw_Items.ItemID = dbo.VW_Vouchers.ItemID
WHERE     (dbo.VW_Vouchers.ItemID IS NOT NULL)
GROUP BY dbo.VW_Vouchers.BankAccountTitle, dbo.vw_Items.ItemName, dbo.VW_Vouchers.BankAccountID, dbo.VW_Vouchers.VoucherDate, 
                      dbo.vw_Items.ItemID, dbo.vw_Items.GroupID, dbo.VW_Vouchers.SessionID, dbo.VW_Vouchers.VoucherNo, dbo.VW_Vouchers.AccountNo, 
                      dbo.VW_Vouchers.AccountTitle
HAVING      (SUM(ISNULL(dbo.VW_Vouchers.Edel, 0)) - SUM(ISNULL(dbo.VW_Vouchers.Erec, 0)) <> 0)
